package com.bw.dao;

import com.bw.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {
    @Select("SELECT oi.category_id1 categoryId1," +
            "oi.category_id2 categoryId2," +
            "oi.category_id3 categoryId3," +
            "DATE_FORMAT(o.pay_time, '%Y-%m-%d') countDate," +
            "SUM(oi.pay_money) money," +
            "SUM(oi.num) num" +
            " from tb_order_item oi, tb_order o" +
            " where oi.order_id = o.id" +
            " and o.pay_status = 1" +
            " and DATE_FORMAT(o.pay_time, '%Y-%m-%d') = #{date}" +
            " GROUP BY oi.category_id1,oi.category_id2,oi.category_id3,DATE_FORMAT(o.pay_time, '%Y-%m-%d')")
    public List<CategoryReport> categoryReport(@Param("date") LocalDate date);

    /**
     *按日期统计一级分类数据
     * @param date1
     * @param date2
     * @return
     * */
    @Select("SELECT c.`name` as categoryName,SUM(r.num) num,SUM(r.money) money " +
            " from tb_category_report r,v_category1 c" +
            " WHERE r.count_date>=#{date1} and r.count_date <= #{date2} and r.category_id1=c.id" +
            " GROUP BY r.category_id1")
    public List<Map> category1Count(@Param("date1") String date1,@Param("date2") String date2);

}
